package ac.manchester.cs.afzal.tcm_processing.sql_handler;



import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Vector;

/**
 * Derive_Distinct_Sentences class converts the
 * <p>Company: The University of Manchester</p>
 *
 * @author Hammad Afzal
 * @version 1.0
 * Last Modified Date: 15th May, 2012
 */
public class SQL_Handler_Terms_Impl extends SQL_Handler_Impl
{

    Vector doc_ids = new Vector();

    /*
     * Returns a integer value of doc_id when document name is given as input
     * @param doc_value
     */
    public int get_document_id_per_doc_value(String doc_value)
    {
        int count = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Doc_ID FROM documents WHERE Name = '" + doc_value + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                count = rs.getInt("Doc_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("Value of SQL Exception in getting Document ID from PMCID : " + sqlex.getMessage());
        }
        return count;
    }

    /*
     * Returns a vector containing occurrence_ids pertaining to given value of 
     * term occurrence.
     * @param occurrence_value
     */
    public Vector get_occurrence_ids_per_occurrence_values(String occurrence_value)
    {
        Vector occurrence_ids = new Vector();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Occurrence_ID FROM Occurrences WHERE " +
                    "Value= '" + occurrence_value + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                occurrence_ids.add(rs.getInt("Occurrence_ID"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Getting Occurrence_ID : " + sqlex.getMessage());
        }
        return occurrence_ids;
    }

   /*
    * Returns the Sen_ID when doc_id and local_sen_id is given as input.
    * @param doc_id
    * @param local_sen_id
    */
    public int get_sentence_id(int doc_id, int local_sen_id)
    {
        int count = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Sen_ID FROM sentences WHERE Doc_ID = '" + doc_id + 
                    "' and Local_Sen_ID = '" + local_sen_id + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                count = rs.getInt("Sen_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Sentenec ID from Document ID and Sentence ID is : " + sqlex.getMessage());
        }

        return count;
    }
    /*
    * Returns the Sen_ID when doc_id and local_sen_id is given as input.
    * @param doc_id
    * @param local_sen_id
    */
    public int get_sentence_id_for_disease_gad(int doc_id, int local_sen_id)
    {
        int count = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Sen_ID FROM sentences_diseases_gad WHERE Doc_ID = '" + doc_id +
                    "' and Local_Sen_ID = '" + local_sen_id + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                count = rs.getInt("Sen_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Sentenec ID from Document ID and Sentence ID is : " + sqlex.getMessage());
        }

        return count;
    }

    /*
    * Returns the Sen_ID when doc_id and local_sen_id is given as input.
    * @param doc_id
    * @param local_sen_id
    */
    public int get_sentence_id_for_env_fac_gad(int doc_id, int local_sen_id)
    {
        int count = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Sen_ID FROM sentences_env_fac_gad WHERE Doc_ID = '" + doc_id +
                    "' and Local_Sen_ID = '" + local_sen_id + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                count = rs.getInt("Sen_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception . Sentenec ID from Document ID and Sentence ID is : " + sqlex.getMessage());
        }

        return count;
    }

    /*
     * Returns the term id when value of term and doc_id is given as input.
     * @param term
     * @param doc_id
     */
    public int get_term_id(String term, int doc_id)
    {
        int term_id = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Term_ID FROM Terms WHERE Value = '" + term + "' and Doc_ID =" + doc_id;
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                term_id = rs.getInt("Term_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Getting Real Term ID from Document ID and Term : " + sqlex.getMessage());
        }
        return term_id;
    }

    public int get_disease_obm_id(String disease_name)
    {
        int disease_obm_id = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Disease_ID FROM Diseases_OBM WHERE Disease  = '" + disease_name + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                disease_obm_id = rs.getInt("Disease_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Getting Real Term ID from Document ID and Term : " + sqlex.getMessage());
        }
        return disease_obm_id;
    }

    /*
     * Returns the Term_ID corresponding to the Occurrence_ID from Term_Mapping table.
     */
    public int get_term_id_from_map(String occurrence_id)
    {
        int term_id = 0;
        try
        {
            statement = connection.createStatement();
            String query = "SELECT Term_ID FROM Term_Mapping WHERE Occurrence_id = '" + occurrence_id + "'";
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                term_id = rs.getInt("Term_ID");
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Getting Term ID from Term Map: " + sqlex.getMessage());
        }
        return term_id;
    }

    /*
     * Returns the Term_ID corresponding to the Occurrence_ID from Term_Mapping table.
     */
    public List<String> get_terms_from_result_views(String view_name)
    {
        List full_terms = new ArrayList();
        try
        {
            statement = connection.createStatement();
            String query = "SELECT distinct Value FROM " + view_name;
            ResultSet rs = statement.executeQuery(query);
            while(rs.next())
            {
                full_terms.add(rs.getString("Value"));
            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception: Getting Term ID from Term Map: " + sqlex.getMessage());
        }
        return full_terms;
    }

    /*
     * Returns a vector containing all values of occurrence ids, when occurrence value is given
     * as input. [ Optional: Document id is also given as input]
     * @param occurrence_value
     * @param doc_id
     */
    public Vector get_occurrence_id_per_occurrence_value(String occurrence_value, String doc_id)
    {
        Vector occurrence_ids = new Vector();
        doc_ids.clear();

        try
        {
            String query = (doc_id.equals("0"))? "SELECT Occurrence_ID, Doc_ID FROM occurrences where Value = '" + occurrence_value + "'":
                                        "SELECT Occurrence_ID FROM occurrences where Value = '" + occurrence_value + "' and Doc_ID = '" + doc_id + "'";

            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery(query);
            if(doc_id.equals("0"))
            {
                while(rs.next())
                {
                    occurrence_ids.add(rs.getInt("Occurrence_ID"));
                    doc_ids.add(rs.getInt("Doc_ID"));
                }
            }
            else
            {
                while(rs.next())
                {
                    occurrence_ids.add(rs.getInt("Occurrence_ID"));
                }

            }
            statement.close();
        }
        catch (SQLException sqlex)
        {
            System.out.println("SQL Exception Getting Occurence ID when Term Occurence is given : " + sqlex.getMessage());
        }
        return occurrence_ids;
    }


    /*
     * Set the "Type" of term, when term_id is given.
     */
    public void update_term_table(int term_id, String type_value)
    {
        try
        {
            Statement stmt = connection.createStatement();
            String query = "UPDATE Terms SET Type = '" + type_value + "' WHERE Term_ID = '" + term_id + "'";
            stmt.executeUpdate(query);
        }
        catch (SQLException ex)
        {
            System.out.println("SQL Exception in updating term table : " + ex.getMessage());
        }
    }

    /*
     * Set the "Type" of term, when term value is given.
     */
    public void update_term_table(String term_value, String type_value)
    {
        try
        {
            Statement stmt = connection.createStatement();
            String query = "UPDATE Terms SET Type = '" + type_value + "' WHERE Value = '" + term_value + "'";
            stmt.executeUpdate(query);
        }
        catch (SQLException ex)
        {
            System.out.println("SQL Exception in updating term table : " + ex.getMessage());
        }
    }

    public Vector get_doc_ids()
    {
        return doc_ids;
    }
}
